package mails.da.jdbc;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import mails.Tools;
import mails.da.MailDAO;
import mails.vo.Mail;

public class JdbcMailDAO extends JdbcDAOFactory implements MailDAO {

	public static final String ID = "id";
	public static final String SUBJECT = "subj";
	public static final String DATE = "date";
	public static final String BODY = "body";
	public static final String NAME = "from_name";
	public static final String ADDRESS = "from_mail";
	public static final String TO = "to_mail";
	public static final String WORDCOUNT = "wordcount";

	public void setDBFilePath(String path) {
		setPath(path);
	}

	public void saveMail(Mail mail) {
		con = createConnection();
		try {
			StringBuilder query = new StringBuilder();
			query.append("UPDATE mails SET ")
	//		.append(SUBJECT).append(" = ?")
	//		.append(BODY).append(" = ?")
	//		.append(NAME).append(" = ?")
	//		.append(ADDRESS).append(" = ?")
	//		.append(TO).append(" = ?")
			.append(WORDCOUNT).append(" = ?")
			.append(" WHERE ")
			.append(ID).append(" = ?");
			
			PreparedStatement pStmt = con.prepareStatement(query.toString());
			

			// here could be other parameters as well. but watch for the counting
			pStmt.setInt(1, mail.getWordcount());
			pStmt.setInt(2, mail.getId());
			
			pStmt.executeUpdate();
			con.close();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}
	}

	public void updateWordcount(List<Mail> mails) {
		con = createConnection();
		
		try {
			
			//disable of the autocommit that all changed are written once
			con.setAutoCommit(false);
			
			String query = "UPDATE mails SET WORDCOUNT = (?) WHERE ID = (?)";
			
			PreparedStatement pStmt = null;
			
			for (Mail mail : mails) {

				pStmt = con.prepareStatement(query);
			
				// here could be other parameters as well. but watch for the counting
				pStmt.setInt(1, mail.getWordcount()); 
				pStmt.setInt(2, mail.getId());
				pStmt.addBatch();
			}
			pStmt.executeBatch();
			con.commit();
			
			con.close();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}
	}
	
	public List<Mail> getMails(String theQuery) {
		List<Mail> result = new ArrayList<Mail>();
		con = createConnection();
		try {
			rs = executeQuery(con, theQuery);
			while (rs.next()) {
				Mail mail = new Mail(rs.getInt(ID), rs.getString(SUBJECT),
						rs.getString(BODY), rs.getString(NAME),
						rs.getString(ADDRESS), rs.getString(TO)
				);

				// parsing the date and time out of the column date
				String[] dateNtime = rs.getString(DATE).split(" ");
				mail.setDate(Date.valueOf(dateNtime[0]).toString());
				mail.setTime(Time.valueOf(dateNtime[1]).toString());
				
				// setting wordcount extra cuz the original tables got altered
				mail.setWordcount(rs.getInt(WORDCOUNT));
				
				// adds the Mail object to the result list
				result.add(mail);
			}
			con.close();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}
		return result;
	}
	
	public List<Mail> getAddressMails(String addr) {
		List<Mail> result = new ArrayList<Mail>();
		
		con = createConnection();
		
		try {
			
				String query = "SELECT * FROM mails WHERE from_mail = (?)";
				
				PreparedStatement pStmt = null;

					pStmt = con.prepareStatement(query);
				
					// here could be other parameters as well. but watch for the counting
					pStmt.setString(1, addr); 

			rs = executeQuery(con, query);
			while (rs.next()) {
				Mail mail = new Mail(rs.getInt(ID), rs.getString(SUBJECT),
						rs.getString(BODY), rs.getString(NAME),
						rs.getString(ADDRESS), rs.getString(TO)
				);

				// parsing the date and time out of the column date
				String[] dateNtime = rs.getString(DATE).split(" ");
				mail.setDate(Date.valueOf(dateNtime[0]).toString());
				mail.setTime(Time.valueOf(dateNtime[1]).toString());
				
				// setting wordcount extra cuz the original tables got altered
				mail.setWordcount(rs.getInt(WORDCOUNT));
				
				// adds the Mail object to the result list
				result.add(mail);
			}
			con.close();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}
		return result;
	}

	public HashMap<String, Integer> getIndexedWords() {
		HashMap<String, Integer> indexedWords = new HashMap<String, Integer>();
		StringBuilder theQuery = new StringBuilder();
		
		theQuery.append("SELECT ")
		.append(JdbcMailDAO.BODY)
		.append(" FROM mails");
		
		con = createConnection();
		
		try{
			rs = executeQuery(con, theQuery.toString());
			while (rs.next()) {
				Tools.wordIndex2(rs.getString(BODY), indexedWords);
			}
			con.close();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}
		
		return indexedWords;
	}
		
}
